package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.dto.TreeNode;
import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.Constants;
import com.hele.sts.pub.SpringContextHolder;

public class TreeDao {

	private static final Logger logger = LoggerFactory.getLogger(TreeDao.class);

	public static List<TreeNode> getZfjList(String nbxh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		sql = "select t.nbxh||'_'||t.zfjbz as id,t.nbxh as pid,t.zfjbz as name,t.ajqx as cxqx from da_qyajzfjb t where nbxh=?";
		TreeNode node = null;
		List<TreeNode> nodeList = new ArrayList<TreeNode>();
		logger.debug(sql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);
		
		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, nbxh);
			rs = pstmt.executeQuery();
		    while (rs.next()) {
		    	node = rsToZfjNode(rs);
		    	nodeList.add(node);
		    }
		    return nodeList;
		} catch (SQLException e) {
			logger.info("db error", e);
		} catch (Exception e) {
			logger.info("other error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return null;
	}

	private static TreeNode rsToZfjNode(ResultSet rs) throws SQLException {
		TreeNode node = new TreeNode();
		node.setId(rs.getString("id"));
		node.setPId(rs.getString("pid"));
		String name = rs.getString("name");
		if("0".equals(name)){
			node.setName("正卷");
		}else {
			node.setName("副卷");
		}
		//node.setName(rs.getString("name"));
		node.setCxqx(rs.getString("cxqx"));
		node.setParent(true);
		node.setCanOpen(true);
		node.setIconSkin("folder");
		return node;
	}
	
	private static TreeNode rsToJnmlNode(ResultSet rs) throws SQLException {
		TreeNode node = new TreeNode();
		node.setId(rs.getString("id"));
		node.setPId(rs.getString("pid"));
		node.setName(rs.getString("name"));
		node.setCxqx(rs.getString("cxqx"));
		//node.setParent(true);
		int cnt = rs.getInt("cnt");
		node.setParent(cnt>0);
		node.setIconSkin("folder");
		node.setCanOpen(true);
		return node;
	}
	
	private static TreeNode rsToInmlNode(ResultSet rs) throws SQLException {
		TreeNode node = new TreeNode();
		node.setId(rs.getString("id"));
		node.setPId(rs.getString("pid"));
		node.setName(rs.getString("name"));
		node.setCxqx(rs.getString("cxqx"));
		node.setParent(false);
		node.setCanOpen(true);
		return node;
	}

	public static List<TreeNode> getJnmlList(String pid,String filterStr) {
		filterStr = "%"+filterStr+"%";
		String[] str = pid.split("_");
		String nbxh =str[0];
		String zfjid = str[1];
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		//sql = "select t.nbxh||'_'||t.zfjbz||'_'||t.jnmlbh as id, t.nbxh||'_'||t.zfjbz as pid, '('||lpad(rownum,3,'0')||')'||s.content||to_char(jnmldjrq,'yyyyMMdd') as name, t.jnmlqx as cxqx from da_qyzfjzmlb t,da_jnmlmcb s where nbxh=? and zfjbz=? and t.jnmlbh=s.code";
		//sql = "select t.nbxh||'_'||t.zfjbz||'_'||t.jnmlbh as id, t.nbxh||'_'||t.zfjbz as pid, '('||lpad(rownum,3,'0')||')'||t.jnmlmc||'_'||to_char(jnmldjrq,'yyyyMMdd') as name, t.jnmlqx as cxqx,(select count(*) as cnt from da_qyjnmlb s where  t.nbxh=s.nbxh and t.jnmlbh=s.jnmlbh) as cnt from da_qyzfjzmlb t "
			//	+ "where nbxh=? and zfjbz=? and t.jnmlmc like ? order by jnmldjrq ";
		//		+ "where nbxh=? and zfjbz=? and  ( ( t.jnmlmc like ? ) or exists (select 1 from da_qyjnmlb v where t.nbxh=v.nbxh and t.jnmlbh=v.jnmlbh and v.clmlmc like ?)) order by jnmldjrq ";
		sql = "select  '('||lpad(rownum,3,'0')||')'||a.name as name,a.* from ("+
				"select t.nbxh||'_'||t.zfjbz||'_'||t.jnmlbh as id, t.nbxh||'_'||t.zfjbz as pid,trim(t.jnmlmc)||'_'||to_char(jnmldjrq,'yyyyMMdd') as name, t.jnmlqx as cxqx,(select count(*) as cnt from da_qyjnmlb s where  t.nbxh=s.nbxh and t.jnmlbh=s.jnmlbh) as cnt from da_qyzfjzmlb t "
				+ "where nbxh=? and zfjbz=? and  ( ( t.jnmlmc like ? ) or exists (select 1 from da_qyjnmlb v where t.nbxh=v.nbxh and t.jnmlbh=v.jnmlbh and v.clmlmc like ?)) order by jnmldjrq )  a";
		TreeNode node = null;
		List<TreeNode> nodeList = new ArrayList<TreeNode>();
		logger.debug(sql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);
		
		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, nbxh);
			pstmt.setString(2, zfjid);
			pstmt.setString(3, filterStr);
			pstmt.setString(4, filterStr);
			rs = pstmt.executeQuery();
		    while (rs.next()) {
		    	node = rsToJnmlNode(rs);
		    	nodeList.add(node);
		    }
		    return nodeList;
		} catch (SQLException e) {
			logger.info("db error", e);
		} catch (Exception e) {
			logger.info("other error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return null;
	}

	public static List<TreeNode> getInmlList(String pid,String filterStr) {
		filterStr = "%"+filterStr+"%";
		String[] str = pid.split("_");
		String nbxh =str[0];
		String zfjbz = str[1];
		String jnmlbh = str[2];
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		sql = "select t.nbxh||'_'||t.jnmlbh||'_'||t.inmlbh||'_'||t.djjg as id, ? as pid,  trim(t.clmlmc)||'('||s.content||')' as name,t.inmlqx as cxqx,k.djjgshow "
				+ "from da_qyjnmlb t,bm_djjg s,xt_server k  where t.djjg=s.code and t.djjg=k.djjg and k.strategy='0' and nbxh=? and jnmlbh=? and ( t.clmlmc like ?  or exists (select 1 from da_qyzfjzmlb v where t.nbxh=v.nbxh and t.jnmlbh=v.jnmlbh and v.jnmlmc like ?)) order by t.inmlbh";
		TreeNode node = null;
		List<TreeNode> nodeList = new ArrayList<TreeNode>();
		logger.debug(sql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);
		
		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pid);
			pstmt.setString(2, nbxh);
			pstmt.setString(3, jnmlbh);
			pstmt.setString(4, filterStr);
			pstmt.setString(5, filterStr);
			rs = pstmt.executeQuery();
		    while (rs.next()) {
		    	node = rsToInmlNode(rs);
		    	String djjgShow = rs.getString("djjgshow");
		    	if(!Constants.DIR_DJJGSHOW_SHOW.equals(djjgShow)){
		    		String newname = node.getName();
		    		newname = newname.substring(0, newname.indexOf("("));
		    		node.setName(newname);
		    	}
		    	nodeList.add(node);
		    }
		    return nodeList;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return null;
	}

	public static List<Qyzfjzmlb> getJnmlList(String nbxh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		sql = "select * from da_qyzfjzmlb t where nbxh=?  order by jnmldjrq ";
		Qyzfjzmlb qyjnml = null;
		List<Qyzfjzmlb> qyjnmlList = new ArrayList<Qyzfjzmlb>();
		logger.debug(sql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);
		
		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, nbxh);
			rs = pstmt.executeQuery();
		    while (rs.next()) {
		    	qyjnml = rsToQyjnml(rs);
		    	qyjnmlList.add(qyjnml);
		    }
		    return qyjnmlList;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return null;
	}

	private static Qyzfjzmlb rsToQyjnml(ResultSet rs) throws SQLException {
		Qyzfjzmlb qyjnml = new Qyzfjzmlb();
		qyjnml.setNbxh(rs.getString("nbxh"));
		qyjnml.setJnmljh(rs.getInt("jnmljh"));;
		qyjnml.setZfjbz(rs.getString("zfjbz"));
		qyjnml.setJnmlbh(rs.getInt("jnmlbh"));
		qyjnml.setJnmlmc(rs.getString("jnmlmc"));
		qyjnml.setJnmlys(rs.getInt("jnmlys"));
		qyjnml.setJnmldjrq(rs.getDate("jnmldjrq"));
		return qyjnml;
	}
	
}
